package com.lhhg.MySQL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.lhhg.DTO.Score;

public class ScoreDAO {
	
	public static Connection getconnection(){
		Connection conn=null;
		try{
			String url="jdbc:mysql://localhost:3306/soccer";
		    String user="root";
		    String pass="root";
            Class.forName("com.mysql.jdbc.Driver");
            conn= DriverManager.getConnection(url,user,pass);
        }
        catch(Exception e)
        {
        	e.printStackTrace();
        }
		return conn;
	}
	
	public static List<Score> getListScore(){
		 List<Score>ds=new ArrayList<Score>();
		 Connection con=getconnection();
			try {
	            
	            Statement stmt = con.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT * FROM score");
				while(rs.next()){
					Score a=new Score();
					a.setResuitID(rs.getInt("ResuiltID"));
					a.setIDTeam1(rs.getString("IDTeam1"));
					a.setIDTeam2(rs.getString("IDTeam2"));
					a.setScore1(rs.getInt("Score1"));
					a.setScore2(rs.getInt("Score2"));
					ds.add(a);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return ds;
	}
	
	public void add(Score score)
    {
		 Connection con = getconnection();
		 ResultSet rs;
         try {
            Statement stsm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			rs = stsm.executeQuery("SELECT * FROM score");
			rs.moveToInsertRow();
			rs.updateInt("ResuiltID", score.getResuitID());
			rs.updateString("IDTeam1", score.getIDTeam1());
			rs.updateString("IDTeam2", score.getIDTeam2());
			rs.updateInt("Score1", score.getScore1());
			rs.updateInt("Score2", score.getScore2());
			rs.insertRow();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
	
	public static void update(Score score)
	{
		 Connection con =getconnection();
			
			try {
				PreparedStatement ps = con.prepareStatement("update score set Score1=?,Score2=? where IDResuilt=?");
				ps.setInt(1, score.getResuitID());
				ps.setInt(2, score.getScore1());
				ps.setInt(3, score.getScore2());
				ps.executeUpdate();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	 }
	public static Score listTeamById(int idResuilt) {
		 List<Score> list= getListScore();
  	  for(Score score:list)
  	  {
  		  if(score.getResuitID()==idResuilt)
  		  {
  			  return score;
  		  }
  	  }
  	  return null;
	}

}
